USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[SyozokuPlayerList]    Script Date: 05/09/2014 18:04:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SyozokuPlayerList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SyozokuPlayerList]
GO

CREATE PROCEDURE [dbo].[SyozokuPlayerList]
	@TAIKAINO int,
	@SYOZOKUNO int
AS
SELECT 
	AAA.KYOGISYANO,
	convert(varchar(15), AAA.NUMBER1) + convert(varchar(15), AAA.NUMBER2) AS NUMBER2,
	AAA.KYOGISYAKANA + '<br>' + AAA.KYOGISYAMEI AS KYOGISYAMEI,
	dbo.Seibetu(AAA.SEIBETU) AS SEIBETU,
	(AAA.GAKUNEN + '<br>' + (CASE AAA.SEINEN WHEN '' THEN '' ELSE '(' + RIGHT(AAA.SEINEN,2) + ')'END)) AS GAKUNEN,
	MAX(CASE num WHEN 1 THEN AAA.KYOGI ELSE '' END) AS S1,
	MAX(CASE num WHEN 2 THEN AAA.KYOGI ELSE '' END) AS S2,
	MAX(CASE num WHEN 3 THEN AAA.KYOGI ELSE '' END) AS S3,
	MAX(CASE num WHEN 4 THEN AAA.KYOGI ELSE '' END) AS S4,
	MAX(CASE num WHEN 5 THEN AAA.KYOGI ELSE '' END) AS S5
FROM
(
	SELECT 
		KS.KYOGISYANO,
		KS.NUMBER1,
		KS.NUMBER2,
		KS.KYOGISYAKANA,
		KS.KYOGISYAMEI,
		KS.SEIBETU,
		KS.GAKUNEN,
		KS.SEINEN,
		SK.KYOGI,
		SK.KIROKUKUBUN,
		SK.KIROKUFLG,
		SK.SYUMOKUKUBUN,
		ROW_NUMBER() OVER(PARTITION BY KS.KYOGISYANO ORDER BY SK.KYOGI) AS num
	FROM
	(
		SELECT KS.TAIKAINO,
			KS.KYOGISYANO,
			KS.NUMBER1,
			KS.NUMBER2,
			KS.KYOGISYAMEI,
			KS.KYOGISYAKANA,
			KS.SEIBETU,
			KS.GAKUNEN,
			KS.SEINEN
		FROM TBL_KYOGISYA AS KS,
			TBL_SYOZOKU AS S
		WHERE KS.TAIKAINO = @TAIKAINO
			AND KS.TAIKAINO = S.TAIKAINO
			AND KS.SYOZOKUNO1 = S.SYOZOKUNO
			AND (KS.KYOGISYAFLG = 0 OR KS.KYOGISYAFLG IS NULL)
			AND S.SYOZOKUNO = @SYOZOKUNO
		UNION
		SELECT KS.TAIKAINO,
			KS.KYOGISYANO,
			KS.NUMBER1,
			KS.NUMBER2,
			KS.KYOGISYAMEI,
			KS.KYOGISYAKANA,
			KS.SEIBETU,
			KS.GAKUNEN,
			KS.SEINEN
		FROM TBL_KYOGISYA AS KS,
			TBL_SYOZOKU AS S
		WHERE KS.TAIKAINO = @TAIKAINO
		AND KS.TAIKAINO = S.TAIKAINO
		AND KS.SYOZOKUNO2 = S.SYOZOKUNO
		AND (KS.KYOGISYAFLG = 0 OR KS.KYOGISYAFLG IS NULL)
		AND S.SYOZOKUNO = @SYOZOKUNO 
	) AS KS
	LEFT OUTER JOIN 
	(
		SELECT WK.TAIKAINO,
			WK.KYOGINO,
			(WK.KYOGIMEI + '<br>' + (CASE WHEN WSK.KIROKU IS NULL THEN '' ELSE 
			dbo.Kiroku(WSK.KIROKU, SM.SYUMOKUKUBUN,SM.KIROKUKUBUN,WSK.KIROKUFLG)END)) AS KYOGI,--20140514
			WSK.KYOGISYANO,
			WSK.KIROKU,
			WSK.OPENFLG,
			SM.KIROKUKUBUN,
			WSK.KIROKUFLG,
			SM.SYUMOKUKUBUN
		FROM TBL_SANKAKYOGI AS WSK,
			TBL_KYOGI AS WK,
			TBL_SYUMOKU_MASTER AS SM
		WHERE WK.TAIKAINO = @TAIKAINO
		AND WSK.TAIKAINO = WK.TAIKAINO
		AND WSK.KYOGINO = WK.KYOGINO
		AND WK.SYUMOKUNO = SM.SYUMOKUNO
		AND WK.GATTAIFLG <> 2 
	)AS SK
	ON KS.KYOGISYANO = SK.KYOGISYANO
) AS AAA
GROUP BY 
	AAA.KYOGISYANO,
	AAA.NUMBER1,
	AAA.NUMBER2,
	AAA.KYOGISYAKANA,
	AAA.KYOGISYAMEI,
	AAA.SEIBETU,
	AAA.GAKUNEN,
	AAA.SEINEN
ORDER BY AAA.NUMBER1, AAA.NUMBER2, AAA.KYOGISYANO
GO